package com.spring.boot.core.utils;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class MySQLUtils {
	
	public static class ExecSQL {
		
		public final String url;
		public final String sql;
		public final String user;
		public final String passwd;
		
		public ExecSQL(String host, String user, String passwd, String db, String sql) {
			this.url = String.format("jdbc:mysql://%s/%s?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull", host, db);
			this.sql = sql;
			this.user = user;
			this.passwd = passwd;
		}
		
		public ExecSQL(String full_host, String user, String passwd, String sql) {
			this.url = full_host;
			this.sql = sql;
			this.user = user;
			this.passwd = passwd;
		}
	}
	
	public static boolean exec(ExecSQL execSQL) {
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(execSQL.url, execSQL.user, execSQL.passwd);
			stmt = conn.createStatement();
			return stmt.execute(execSQL.sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) try { stmt.close(); } catch (Exception e2) {}
			if (conn != null) try { conn.close(); } catch (Exception e2) {}
		}
		return false;
	}
	
	public static JSONArray query(ExecSQL execSQL) {
		JSONArray ret = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(execSQL.url, execSQL.user, execSQL.passwd);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(execSQL.sql);
			ret = convert2(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) try { rs.close(); } catch (Exception e2) {}
			if (stmt != null) try { stmt.close(); } catch (Exception e2) {}
			if (conn != null) try { conn.close(); } catch (Exception e2) {}
		}
		return ret;
	}
	
	public static JSONArray convert2(ResultSet rs) throws Exception {
		JSONArray json = new JSONArray();
		ResultSetMetaData rsmd = rs.getMetaData();
		int numColumns = rsmd.getColumnCount();
		JSONArray th = new JSONArray();
		for (int i=1; i<numColumns+1; i++) {
			th.add(rsmd.getColumnName(i));
		}
		json.add(th);
		while(rs.next()) {
			JSONArray td = new JSONArray();
			for (int i=1; i<numColumns+1; i++) {
				if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
					td.add(rs.getArray(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.BIGINT){
					td.add(rs.getInt(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
					td.add(rs.getBoolean(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.BLOB){
					td.add(rs.getBlob(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
					td.add(rs.getDouble(i)); 
				} else if (rsmd.getColumnType(i)==java.sql.Types.FLOAT){
					td.add(rs.getFloat(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.INTEGER){
					td.add(rs.getInt(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
					td.add(rs.getNString(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
					td.add(rs.getString(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.TINYINT){
					td.add(rs.getInt(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
					td.add(rs.getInt(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.DATE){
					td.add(rs.getDate(i));
				} else if (rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
					td.add(rs.getTimestamp(i));   
				} else {
					td.add(rs.getObject(i));
				}
			}
			json.add(td);
		}
		return json;
	}

	public static JSONArray convert(ResultSet rs) throws Exception {
		JSONArray json = new JSONArray();
		ResultSetMetaData rsmd = rs.getMetaData();
		while(rs.next()) {
			int numColumns = rsmd.getColumnCount();
			JSONObject obj = new JSONObject();
			for (int i=1; i<numColumns+1; i++) {
				String column_name = rsmd.getColumnName(i);
				if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
					obj.put(column_name, rs.getArray(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.BIGINT){
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
					obj.put(column_name, rs.getBoolean(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.BLOB){
					obj.put(column_name, rs.getBlob(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
					obj.put(column_name, rs.getDouble(column_name)); 
				} else if (rsmd.getColumnType(i)==java.sql.Types.FLOAT){
					obj.put(column_name, rs.getFloat(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.INTEGER){
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
					obj.put(column_name, rs.getNString(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
					obj.put(column_name, rs.getString(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.TINYINT){
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.DATE){
					obj.put(column_name, rs.getDate(column_name));
				} else if (rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
					obj.put(column_name, rs.getTimestamp(column_name));   
				} else {
					obj.put(column_name, rs.getObject(column_name));
				}
			}
			json.add(obj);
		}
		return json;
	}
	
	public static List<String> parseFile(String sql_file) throws Exception {
		List<String> ret = new ArrayList<String>();
		String file_dir = sql_file.substring(0, sql_file.lastIndexOf('/') + 1);
		BufferedReader reader = new BufferedReader(new FileReader(sql_file));
		StringBuffer sqlBuf = new StringBuffer();
		String line;
		boolean skip = false;
		while ((line = reader.readLine()) != null) {
			line = line.trim();
			if (line.toUpperCase().startsWith("DELIMITER")) {
				skip = !line.endsWith(";");
				continue;
			} else if (skip || line.startsWith("--")) {
				continue;
			}
			
			sqlBuf.append(line);
			if (line.endsWith(";")) {
				String sql = sqlBuf.toString().trim();
				sqlBuf = new StringBuffer();
				if (sql.startsWith("source ")) {
					String source_file = sql.replace("source", "").replace(";", "").trim();
					ret.addAll(parseFile(file_dir + source_file));
				} else {
					ret.add(sql);
				}
			} else {
				sqlBuf.append(" ");
			}
		}
		reader.close();
		return ret;
	}
	
	public static void main(String[] args) throws Exception{
		System.out.println(JSONObject.toJSONString(query(new ExecSQL("localhost:3306", "root", "onlyku123", "platformdb", "select * from game_cluster"))));
		System.out.println(JSONObject.toJSONString(exec(new ExecSQL("jdbc:mysql://localhost:3306", "root", "onlyku123", "drop database gamedb"))));
		
	}
}
